Alter Index
In this lesson we discuss how to modify an index.
We'll cover the following
ALTER INDEX#
It is hard to predict what indexes to create without observing the access patterns for an application. We can add, remove, or modify indexes after the application is deployed. Note that modifying indexes doesn’t change the data in the table.
Example Syntax#
ALTER TABLE table
ADD INDEX indexName (col1, col2, … coln);
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/18lesson.sh and wait for the MySQL prompt to start-up.
-
MySQL allows us to add a new index to an existing table. Say we find out that a lot of users of our application are searching actors by first name. We can speed up their queries by declaring an index on the first name column as follows:
ALTER TABLE Actors ADD INDEX nameIndex (FirstName);
If we want to create the index on the first name column but use only the first ten characters, the query would look like as follows:
ALTER TABLE Actors ADD INDEX nameIndexWithOnlyTenChars (FirstName(10));
-
We can also delete the index we just created as follows:
ALTER TABLE Actors DROP INDEX nameIndex;
We can’t add a second primary key to a table that already has a primary key. However, we can drop the existing primary key and declare a new one on the table. In the case of the Actors table, we can’t drop the primary key ID as it is an auto_increment column and an auto_increment column must also be the primary key. Attempting to drop the ID column as the primary key results in the following error:
ALTER TABLE Actors DROP PRIMARY KEY;
As an example, we’ll create a temporary table Movies with just two columns, name and release date. Next, we’ll demonstrate how to delete the primary key and then declare the other column to be the primary key for the table.
1. CREATE TABLE Movies (Name VARCHAR(100), Released DATE, PRIMARY KEY (Name));
2. DESC Movies;
3. ALTER TABLE Movies DROP PRIMARY KEY;
4. ALTER TABLE Movies ADD PRIMARY KEY (Released);